package br.com.abenildo.modelo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.com.abenildo.controle.Biblioteca;
import br.com.abenildo.exception.BibliotecaNaoEncontradoException;
import br.com.abenildo.exception.RepositorioException;

public class RepositorioBibliotecaBDR {

	private Connection connection = null;
	private Biblioteca biblioteca;

	public RepositorioBibliotecaBDR() throws Exception {
		this.connection = ConnectionManager.reservaStatement("oracle");
	}

	public void Inserir(Biblioteca biblioteca) throws RepositorioException,
			SQLException {
		PreparedStatement stmt = null;

		if (biblioteca != null) {
			try {
				String sql = "INSERT INTO biblioteca (CNPJ,NOME_BIBLI,END,CONTATO) VALUES (12345678901,?,?,?)";
				stmt = this.connection.prepareStatement(sql);
				stmt.setString(1, biblioteca.getCnpj());
				stmt.setString(2, biblioteca.getNome_Bibli());
				stmt.execute();
			} catch (SQLException ex) {
				throw new RepositorioException(ex);
			} finally {
				stmt.close();
			}
		}
	}

	public void Remover(String cnpj) throws BibliotecaNaoEncontradoException,
			RepositorioException, SQLException {
		PreparedStatement stmt = null;

		try {
			String sql = "DELETE FROM biblioteca WHERE CNPJ = ?";
			stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, cnpj);
			stmt.execute();
		} catch (SQLException ex) {
			throw new RepositorioException(ex);
		} finally {
			stmt.close();
		}

	}

	public Biblioteca Procurar(String cnpj)
			throws BibliotecaNaoEncontradoException, RepositorioException,
			SQLException {
		Biblioteca biblioteca = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String sql = "SELECT *FROM biblioteca WHERE cpf = ? ";
			stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, cnpj);
			rs = stmt.executeQuery();

			// SE O PROXIMO FOR DIFERENTE LEVANTE UMA NOVA EXCESSAO
			if (!rs.next())
				throw new BibliotecaNaoEncontradoException(cnpj);

			biblioteca = new Biblioteca(rs.getString("CNPJ"),
					rs.getString("NOME_BIBLI"), rs.getString("END"),
					rs.getString("CONTATO"));
		} catch (SQLException ex) {
			throw new RepositorioException(ex);
		}

		finally {

			stmt.close();

			rs.close();

		}
		return biblioteca;

	}

	public void Atualizar(Biblioteca biblioteca)
			throws BibliotecaNaoEncontradoException, RepositorioException,
			SQLException {

		PreparedStatement stmt = null;

		try {
			if (biblioteca != null)
				;

			try {
				String sql = "UPDATE biblioteca SET NOME_BIBLI = ?";
				sql += "where CNPJ = ?";
				stmt = this.connection.prepareStatement(sql);
				stmt.setString(1, biblioteca.getNome_Bibli());
				stmt.setString(2, biblioteca.getCnpj());
				Integer resultado = stmt.executeUpdate();

				// SE FOR == 0 LEVANTE UMA NOVA EXCESSAO RETORNANDO O CNPJ
				if (resultado == 0)
					throw new BibliotecaNaoEncontradoException(
							biblioteca.getCnpj());
			} catch (SQLException ex) {
				throw new RepositorioException(ex);
			}
		} finally {
			stmt.close();
		}
	}

	public boolean Existe(String cnpj) throws RepositorioException,
			SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String sql = "SELECT CONT(*) AS QUANTIDADE FROM biblioteca WHERE CNPJ = ?";
			stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, biblioteca.getCnpj());
			rs = stmt.executeQuery();
			rs.next();

			if (rs.getInt("quantidade") == 0) {
				return false;
			} else {
				return true;
			}
		} catch (SQLException ex) {
			throw new RepositorioException(ex);
		} finally {
			stmt.close();
			rs.close();
		}

	}

	public ArrayList<Biblioteca> Listar() throws SQLException {
		ArrayList<Biblioteca> bibliotecas = new ArrayList<Biblioteca>();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String sql = "SELECT *FROM biblioteca";
			stmt = this.connection.prepareStatement(sql);
			rs = stmt.executeQuery();

			while (rs.next()) {
				Biblioteca biblioteca = new Biblioteca(rs.getString("CNPJ"),
						rs.getString("NOME_BIBLI"), rs.getString("END"),
						rs.getString("CONTATO"));
				bibliotecas.add(biblioteca);
				System.out.println(rs.getString("CNPJ"));
			}

		} catch (SQLException ex) {
			throw new SQLException(ex.getMessage());
		} finally {
			stmt.close();
			rs.close();
		}
		return bibliotecas;
	}

}
